package org.derusse.cycle.database;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;
import android.provider.BaseColumns;

public class StatsTable {

	public static final class Structure implements BaseColumns {
		// This class cannot be instantiated
		private Structure() {
		}

		public static final String TABLE_NAME = "location";

		public static final String LATITUDE = "latitude";
		public static final String LONGITUDE = "longitude";
		public static final String ALTITUDE = "altitude";
		public static final String DISTANCE = "distance";
		public static final String TRACE = "trace";
		public static final String FIX_DATE = "date";
	}

	public static void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + Structure.TABLE_NAME + " ("
				+ Structure.LATITUDE + " FLOAT," + Structure.LONGITUDE
				+ " FLOAT," + Structure.ALTITUDE + " INTEGER,"
				+ Structure.DISTANCE + " FLOAT," + Structure.TRACE + " LONG,"
				+ Structure.FIX_DATE + " LONG);");
	}

	public static void onUpgrade(SQLiteDatabase db, int oldVersion,
			int newVersion) {
		if (oldVersion < 3) {
			db.execSQL("DROP TABLE IF EXISTS " + Structure.TABLE_NAME);
			onCreate(db);
		}
	}

	public static void Clean(SQLiteDatabase db) {
		db.delete(Structure.TABLE_NAME, Structure.FIX_DATE + " < ?",
				new String[] { String
						.valueOf(System.currentTimeMillis() - 600000) });
	}

	public static void Insert(SQLiteDatabase db, Location location,
			double distance, double trace) {
		ContentValues values = new ContentValues();
		values.put(Structure.LATITUDE, location.getLatitude());
		values.put(Structure.LONGITUDE, location.getLongitude());
		values.put(Structure.DISTANCE, distance);
		values.put(Structure.ALTITUDE, (location.hasAltitude() ? location
				.getAltitude() : -1));
		values.put(Structure.TRACE, trace);
		values.put(Structure.FIX_DATE, location.getTime());

		db.insert(Structure.TABLE_NAME, null, values);
	}

	public static Cursor SelectAvg(SQLiteDatabase db, int delta) {
		Cursor cursor = db.query(Structure.TABLE_NAME, new String[] {
				"MIN(" + Structure.FIX_DATE + ")",
				"MAX(" + Structure.FIX_DATE + ")",
				"SUM(" + Structure.DISTANCE + ")" }, Structure.FIX_DATE
				+ " >= ?", new String[] { String.valueOf(System
				.currentTimeMillis()
				- delta * 1000) }, null, null, null);
		return cursor;
	}

	public static Cursor SelectTilt(SQLiteDatabase db, double trace) {
		Cursor cursor = db.query(Structure.TABLE_NAME, new String[] {
				Structure.ALTITUDE, Structure.TRACE }, Structure.TRACE
				+ " >= ? AND " + Structure.ALTITUDE + " != -1",
				new String[] { String.valueOf(trace - 20) }, null, null,
				Structure.TRACE);
		return cursor;
	}
}
